﻿using Dapper;
using SQLitePCL;
using System.Data;

namespace MVVM.NotifyPropertyChanged.Services
{
    public class DatabaseInitializer
    {
        private readonly IDbConnection _dbConnection;

        public DatabaseInitializer(IDbConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }

        public void InitializeDatabase()
        {
            Batteries.Init();
            _dbConnection.Open();
            _dbConnection.Execute(createTableQuery);

            // 检查是否已有用户数据，若无则添加
            var existingUser = _dbConnection.QueryFirstOrDefault(selectUserQuery,
                new { Username = "admin" });
            if (existingUser == null)
            {
                _dbConnection.Execute(insertUserQuery,
                    new { Username = "admin", Password = "123456" });
            }
            _dbConnection.Close();
        }

        #region SQL
        /// <summary>
        /// 创建表SQL
        /// </summary>
        private const string createTableQuery = @"
                CREATE TABLE IF NOT EXISTS Users (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Username TEXT NOT NULL,
                    Password TEXT NOT NULL
                );
            ";
        /// <summary>
        /// 插入数据SQL
        /// </summary>
        private const string insertUserQuery = @"
                INSERT INTO Users (Username, Password) 
                VALUES (@Username, @Password)
            ";
        /// <summary>
        /// 查询数据SQL
        /// </summary>
        private const string selectUserQuery = @"
                SELECT * FROM Users
                WHERE Username = @Username";
        #endregion
    }
}
